using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using GeneralLibs;

namespace Lists
{
    public class SoDuTheoDoiTuong
    {
        #region ThoiDiemKhoaSoID property
        protected Guid _ThoiDiemKhoaSoID;
        public Guid ThoiDiemKhoaSoID
        {
            get
            {
                return _ThoiDiemKhoaSoID;
            }
            set
            {
                _ThoiDiemKhoaSoID = value;
            }
        }
        #endregion

        #region TaiKhoanID property
        protected Guid _TaiKhoanID;
        public Guid TaiKhoanID
        {
            get
            {
                return _TaiKhoanID;
            }
            set
            {
                _TaiKhoanID = value;
            }
        }
        #endregion

        #region DoiTuongNghiepVuID property
        protected Guid _DoiTuongNghiepVuID;
        public Guid DoiTuongNghiepVuID
        {
            get
            {
                return _DoiTuongNghiepVuID;
            }
            set
            {
                _DoiTuongNghiepVuID = value;
            }
        }
        #endregion

        #region DuNo property
        private decimal _DuNo;
        public decimal DuNo
        {
            get
            {
                return _DuNo;
            }
            set
            {
                _DuNo = value;
            }
        }
        #endregion

        #region DuCo property
        private decimal _DuCo;
        public decimal DuCo
        {
            get
            {
                return _DuCo;
            }
            set
            {
                _DuCo = value;
            }
        }
        #endregion

        public virtual void InsertOrUpdate2DB()
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            SqlTransaction transaction = null;
            try
            {
                con.Open();
                transaction = con.BeginTransaction();
                SqlCommand cmd = con.CreateCommand();
                cmd.Transaction = transaction;
                InsertOrUpdate2DB(cmd);
                transaction.Commit();
            }
            catch (SqlException ex)
            {
                try
                {
                    transaction.Rollback();
                }
                catch (SqlException transEx)
                {
                    if (transaction.Connection != null)
                    {
                        System.Diagnostics.Trace.WriteLine("An exception of type " + transEx.GetType() + " was encountered while attempting to roll back the transaction.");
                    }
                }
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public virtual void InsertOrUpdate2DB(SqlCommand cmd)
        {
            try
            {
                cmd.CommandText = string.Format("DELETE FROM SoDuTheoDoiTuong WHERE ThoiDiemKhoaSoID = '{0}' AND TaiKhoanID = '{1}' AND DoiTuongNghiepVuID = '{2}'",
                                           ThoiDiemKhoaSoID, TaiKhoanID, DoiTuongNghiepVuID);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO SoDuTheoDoiTuong(ThoiDiemKhoaSoID, TaiKhoanID, DoiTuongNghiepVuID, DuNo, DuCo) " +
                                                "VALUES(@ThoiDiemKhoaSoID, @TaiKhoanID, @DoiTuongNghiepVuID, @DuNo, @DuCo)";
                cmd.Parameters.Clear();

                cmd.Parameters.Add(new SqlParameter("ThoiDiemKhoaSoID", SqlDbType.UniqueIdentifier));
                cmd.Parameters["ThoiDiemKhoaSoID"].Value = ThoiDiemKhoaSoID;

                cmd.Parameters.Add(new SqlParameter("TaiKhoanID", SqlDbType.UniqueIdentifier));
                cmd.Parameters["TaiKhoanID"].Value = TaiKhoanID;

                cmd.Parameters.Add(new SqlParameter("DoiTuongNghiepVuID", SqlDbType.UniqueIdentifier));
                cmd.Parameters["DoiTuongNghiepVuID"].Value = DoiTuongNghiepVuID;

                cmd.Parameters.Add(new SqlParameter("DuNo", SqlDbType.Decimal));
                cmd.Parameters["DuNo"].Value = DuNo;

                cmd.Parameters.Add(new SqlParameter("DuCo", SqlDbType.Decimal));
                cmd.Parameters["DuCo"].Value = DuCo;

                cmd.ExecuteNonQuery();
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static SoDuTheoDoiTuong GetFromDB(SqlCommand cmd, Guid thoiDiemKhoaSoID, Guid taiKhoanID, Guid doiTuongNghiepVuID)
        {
            try
            {
                cmd.CommandText = string.Format("SELECT * FROM SoDuTheoDoiTuong \n" +
                                "WHERE ThoiDiemKhoaSoID = '{0}' AND TaiKhoanID = '{1}' AND DoiTuongNghiepVuID = '{2}'",
                                thoiDiemKhoaSoID, taiKhoanID, doiTuongNghiepVuID);
                SqlDataReader dr = cmd.ExecuteReader();

                SoDuTheoDoiTuong soDu = new SoDuTheoDoiTuong();
                soDu.ThoiDiemKhoaSoID = thoiDiemKhoaSoID;
                soDu.TaiKhoanID = taiKhoanID;
                soDu.DoiTuongNghiepVuID = doiTuongNghiepVuID;
                if (dr.Read())
                {
                    soDu.DuNo = (decimal)dr["DuNo"];
                    soDu.DuCo = (decimal)dr["DuCo"];
                }
                else
                {
                    soDu.DuNo = 0;
                    soDu.DuCo = 0;
                }
                dr.Close();
                return soDu;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static SoDuTheoDoiTuong CalculateFromDB(SqlCommand cmd, DateTime thoiDiem, Guid taiKhoanID, Guid doiTuongNghiepVuID)
        {
            try
            {
                SoDuTheoDoiTuong soDu = new SoDuTheoDoiTuong();

                cmd.CommandText = "Select_SoDuTaiKhoanTheoDoiTuong";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("ThoiDiem", thoiDiem);
                cmd.Parameters.AddWithValue("TaiKhoanID", taiKhoanID);
                cmd.Parameters.AddWithValue("DoiTuongNghiepVuID", doiTuongNghiepVuID);
                SqlDataReader dr = cmd.ExecuteReader();
                soDu.TaiKhoanID = taiKhoanID;
                soDu.DoiTuongNghiepVuID = doiTuongNghiepVuID;
                if (dr.Read())
                {
                    if ((decimal)dr["DuNo"] > (decimal)dr["DuCo"])
                    {
                        soDu.DuNo = (decimal)dr["DuNo"] - (decimal)dr["DuCo"];
                        soDu.DuCo = 0;
                    }
                    else
                    {
                        soDu.DuCo = (decimal)dr["DuCo"] - (decimal)dr["DuNo"];
                        soDu.DuNo = 0;
                    }
                }
                dr.Close();
                return soDu;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
    }
}
